﻿Public Class funcionario_data : Inherits Adaptador

    Event ErrorServer(ByVal Descripcion As String) 'evento de error que propaga el error de la clase adaptador (conexion a base de datos)
    Event GuardaCorrecto() 'evento propio de la clase, cuando guarda o realiza alguna operacion con exito.

    ''' <summary>
    ''' Devuelve todos los registros
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetData() As DataTable
        Conectar()
        Try
            Dim SQL As String
            SQL = "SELECT F.*, G.GRUPO_NOMBRE FROM FUNCIONARIO AS F, GRUPO AS G WHERE F.GRUPO_ID = G.GRUPO_ID ORDER BY FUN_NOMBRE"
            comando.CommandText = SQL
            comando.Connection = conn
            myReader = comando.ExecuteReader
            Dim dt As DataTable = Me.GetTable(myReader)
            Desconectar()
            Return dt
        Catch ex As Exception
            Desconectar()
            RaiseEvent ErrorServer(ex.Message)
        End Try
    End Function

    Public Function msoIdGrupoxNombre(ByVal nombre As String) As Integer
        Conectar()
        Try
            Dim ID As Integer = 0
            Dim SQL As String
            SQL = "SELECT * FROM GRUPO WHERE GRUPO_NOMBRE = '" & nombre & "'"
            comando.CommandText = SQL
            comando.Connection = conn
            myReader = comando.ExecuteReader
            Dim dt As DataTable = Me.GetTable(myReader)
            If Not IsNothing(dt) Then
                If dt.Rows.Count > 0 Then
                    ID = dt.Rows(0).Item(0)
                End If
            End If
            Desconectar()
            Return ID
        Catch ex As Exception
            Desconectar()
            RaiseEvent ErrorServer(ex.Message)
        End Try
    End Function

    Public Function msoBuscarxGrupo(ByVal id_grupo As Integer) As DataTable
        Conectar()
        Try
            Dim SQL As String
            SQL = "SELECT * FROM FUNCIONARIO WHERE GRUPO_ID = " & id_grupo & " ORDER BY FUN_NOMBRE"
            comando.CommandText = SQL
            comando.Connection = conn
            myReader = comando.ExecuteReader
            Dim dt As DataTable = Me.GetTable(myReader)
            Desconectar()
            Return dt
        Catch ex As Exception
            Desconectar()
            RaiseEvent ErrorServer(ex.Message)
        End Try
    End Function


    Public Function ObtenerGrupos() As DataTable
        Conectar()
        Try
            Dim SQL As String
            SQL = "SELECT * FROM GRUPO ORDER BY GRUPO_NOMBRE"
            comando.CommandText = SQL
            comando.Connection = conn
            myReader = comando.ExecuteReader
            Dim dt As DataTable = Me.GetTable(myReader)
            Desconectar()
            Return dt
        Catch ex As Exception
            Desconectar()
            RaiseEvent ErrorServer(ex.Message)
        End Try
    End Function

    ''' <summary>
    ''' Buscar registros por nombre
    ''' </summary>
    ''' <param name="rut"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function msoBuscar(ByVal rut As String) As DataTable
        Conectar()
        Try
            Dim SQL As String
            SQL = "SELECT * FROM FUNCIONARIO WHERE FUN_RUT = '" & rut & "'"
            comando.CommandText = SQL
            comando.Connection = conn
            myReader = comando.ExecuteReader
            Dim dt As DataTable = Me.GetTable(myReader)
            Desconectar()
            Return dt
        Catch ex As Exception
            Desconectar()
            RaiseEvent ErrorServer(ex.Message)
        End Try
    End Function

    ''' <summary>
    ''' Buscar registros por LIKE nombre
    ''' </summary>
    ''' <param name="NOMBRE"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function msoBuscarLike(ByVal NOMBRE As String) As DataTable
        Conectar()
        Try
            Dim SQL As String
            SQL = "SELECT * FROM FUNCIONARIO WHERE FUN_NOMBRE LIKE '%" & NOMBRE & "%'"
            comando.CommandText = SQL
            comando.Connection = conn
            myReader = comando.ExecuteReader
            Dim dt As DataTable = Me.GetTable(myReader)
            Desconectar()
            Return dt
        Catch ex As Exception
            Desconectar()
            RaiseEvent ErrorServer(ex.Message)
        End Try
    End Function


    ''' <summary>
    ''' guardar y actualizar
    ''' </summary>
    ''' <param name="nombre"></param>
    ''' <param name="unidad"></param>
    ''' <param name="grado"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function minGuarda(rut As String, _
                                    ByVal nombre As String, _
                                    ByVal unidad As String, _
                                    ByVal grado As String, _
                                    ByVal grupo_id As Integer, _
                                    edad As Integer, _
                                    peso As Double, _
                                    estatura As Double, _
                                    imc As Double)

        Try
            Dim SQL As String
            Dim dt As DataTable = msoBuscar(rut)
            Conectar()
            If dt.Rows.Count = 0 Then

                SQL = "INSERT INTO FUNCIONARIO(FUN_RUT, FUN_NOMBRE, FUN_UNIDAD, FUN_GRADO, GRUPO_ID, FUN_EDAD, FUN_PESO, FUN_ESTATURA, FUN_IMC) " _
                                         & "values(" _
                                         & "'" & rut & "'," _
                                         & "'" & nombre & "'," _
                                         & "'" & unidad & "'," _
                                         & "'" & grado & "'," _
                                         & "'" & grupo_id & "'," _
                                         & "'" & edad & "'," _
                                         & "'" & peso & "'," _
                                         & "'" & estatura & "'," _
                                         & "'" & Replace(imc, ",", ".") & "')"
            Else
                SQL = "UPDATE FUNCIONARIO SET " _
                                    & " FUN_NOMBRE = '" & nombre & "', " _
                                    & " FUN_UNIDAD = '" & unidad & "', " _
                                    & " FUN_GRADO = '" & grado & "', " _
                                    & " GRUPO_ID = '" & grupo_id & "', " _
                                    & " FUN_EDAD = '" & edad & "', " _
                                    & " FUN_PESO = '" & peso & "', " _
                                    & " FUN_ESTATURA = '" & estatura & "', " _
                                    & " FUN_IMC = '" & Replace(imc, ",", ".") & "' " _
                                    & " WHERE FUN_RUT = '" & rut & "'"
            End If
            comando.CommandText = SQL
            comando.Connection = conn
            comando.ExecuteNonQuery()
            Desconectar()
            RaiseEvent GuardaCorrecto()
        Catch ex As Exception
            Desconectar()
            RaiseEvent ErrorServer(ex.Message)
        End Try
    End Function

    ''' <summary>
    ''' Eliminar registro
    ''' </summary>
    ''' <param name="NOMBRE"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function mdnElimina(ByVal NOMBRE As String)

        Try
            Dim SQL As String
            Conectar()
            SQL = "DELETE FROM FUNCIONARIO WHERE FUN_NOMBRE = '" & NOMBRE & "'"
            comando.CommandText = SQL
            comando.Connection = conn
            comando.ExecuteNonQuery()
            Desconectar()
            RaiseEvent GuardaCorrecto()
        Catch ex As Exception
            Desconectar()
            RaiseEvent ErrorServer(ex.Message)
        End Try
    End Function


    ''' <summary>
    ''' Devuelve error especifico de la conexion (clase adaptador)
    ''' </summary>
    ''' <param name="Descripcion"></param>
    ''' <remarks></remarks>
    Private Sub Adaptador_ErrorBaseServer(ByVal Descripcion As String) Handles Me.ErrorBaseServer
        RaiseEvent ErrorServer(Descripcion)
    End Sub

End Class
